<?php

class ToolsController extends Controller
{
	
	public $layout='application.modules.admin.views.layouts.default';
	
	protected $db;
	

	public function actionIndex()
	{
		//显示数据中的所有数据表
		$tables = $this->showTables();
		
		$this->render('index',array(
			'dbs'=>$dbs,
			'tables'=>$tables
		));
	}
	
	public function actionDbList()
	{
		$this->blackLayout();
		//所有备份文件//
		$path = '.'.Yii::app()->params['dbBackupPath'];
		
		if(!is_dir($path))
		{
			UtilTools::createFolder($path);
		}
		
		$dir = dir($path);
		$i = 0;
		while (false !== ($entry = $dir->read()))
		{
			if(!($entry == '.' || $entry == '..'))
			{
				$file = $path.$entry;
				$dbs[$i]['name'] = UtilTools::getFileName($entry);
				$dbs[$i]['time'] = filectime($file);
				$dbs[$i]['size'] = filesize($file);
			}
			$i++;
		}
		
		foreach ($dbs as $k=>$v)
		{
			$name[$k] = $v['name'];
			$time[$k] = $v['time'];
			$size[$k] = $v['size'];
		}
		
		array_multisort($time,$size,$name,$dbs);
		
		$dbs = json_decode(json_encode(array_reverse($dbs)));

		$this->render('dblist',array(
			'dbs'=>$dbs
		));
	}
	


	public function actionSaveTables() {
		
		if(isset($_POST['table'])&&!empty($_POST['table']))
		{
			//UtilTools::dump($_REQUEST);
			
			$tables = $_POST['table'];
			
			$tablesArr = explode(',', $tables);
			
			//UtilTools::dump($tablesArr);
			
			$path = '.'.Yii::app()->params['dbBackupPath'];
			
			$count = 0;
			
			foreach ($tablesArr as $table)
			{
				$count = $this->saveTable($table, $path, $count);
			}	

			if($count == count($tablesArr))
				echo "ok";
			else 
				echo (count($tablesArr) - $count);
		}
		else 
		{
			throw new CException(404, "The request page doesn't exist");
		}
		


	}
	
	public function saveTable($table, $path, &$count)
	{		

		$filename = $path.$table.date('YmdHis',time()).'.sql';
		
//		echo $filename;
			

// 		die();
		$fp = fopen($filename, 'wb');
		$this->dumpTableAsFile($table, $fp);
		fclose($fp);
			
		$count++;
		
		return $count;
			

	}

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	
	
	*/
	
	/**
	 * 以下方法用于备份数据
	 * Enter description here ...
	 */
	
	public function actionDbBack()
	{


		$path = '.'.Yii::app()->params['dbBackupPath'];
		if(isset($_POST['filename'])&& !empty($_POST['filename']))
		{
			$filename = $path.$_POST['filename'].'.sql';
		}
		else 
		{
			$filename = $path;
		}

		if($this->backup($filename))
		{
			echo 'OK';
		}
		else 
		{
			echo 'fail';
		}
		
//		if(!is_dir($path))
//		{
//			UtilTools::createFolder($path);
//		}
//		
//		$dir = dir($path);
//		while (false !== ($entry = $dir->read()))
//		{
//			if(!($entry == '.' || $entry == '..'))
//			{
//				$dbs[] = UtilTools::getFileName($entry);
//			}
//			
//		}
//		
//		$dbs = array_reverse($dbs);
//		
//		$this->render('backup',array(
//			'dbs'=>$dbs
//		));
		
	

//		var_dump(Yii::app()->db->connectionString);
		

	}
	
	public function actionReset()
	{
		$this->render('reset');
	}
	
	public function actionInsertTest()
	{
//		$sql = "INSERT INTO '{{article}}' VALUES (NULL, '3', 'test2', '0', '2', '0', '0', '1289837645', 'te', '4', '');";
		
//		$sql = "INSERT INTO `uu_article` VALUES (NULL, '3', '表示问候', '0', '2', '5', '0', '1302588167', '问候', '3', '太好不，呵呵<br />');";

		$sql = "INSERT INTO `uu_article` VALUES (NULL, '2,3', '测试', '0', '1', '5', '1304071400', '1302616411', '测试', '3', '真的可以吗？');";
		$result = Yii::app()->db->createCommand($sql)->query();
		if($result)
		{
			echo "OK";
		}
		else
		{
			echo "false";
		}
		
	}
	
	public function actionRestore()
	{
		/**
		 * uurewrite20110505111458
		 * uurewrite20110505111453
		 */
		header("Content-type:UTF8");
		$path = '.'.Yii::app()->params['dbBackupPath'].$_GET['id'].'.sql';
		if(file_exists($path))
		{
			$this->restore($path);
		}
	}
	
	public function actionBatch()
	{
		$ids = $_POST['ids'];
		
		$idArr = explode(',', $ids);
		
		$path = '.'.Yii::app()->params['dbBackupPath'];
		
		$count = 0;
		
		foreach ($idArr as $id)
		{
			$file = $path.$id.'.sql';
			
// 			echo $file;
			
			$this->delete($file, $count);
		}
		
		if($count == count($idArr))
			echo "ok";
		else 
			echo (count($idArr) - $count);
		
		
		
	}
	
	public function delete($file,&$count)
	{
		if(file_exists($file))
		{
			unlink($file);

			$count++;
		}
		
		return $count;
	}
	
	
	//获取数据库名称
	public function getDbName()
	{
		$dbname = "";
		$dbinfo = explode(';', Yii::app()->db->connectionString);
		foreach ($dbinfo as $info)
		{
			$table = explode('=', $info);
			$dbname = $table[1];
		}
		return $dbname;
	}
	
	//备份数据
	public function backup($backupFile = null, $tables = array())
	{
		if(empty($backupFile))
		{
			$backupFile = Yii::app()->params['dbBackupPath'].$this->getDbName().date('Ymdhis').'.sql';
		}
		elseif (is_dir($backupFile))
		{
			//判断$backupFile是否为目录
			if(preg_match('/\/$/', $backupFile))
			{
				$backupFile = $backupFile.$this->getDbName().date('Ymdhis').'.sql';
			}
			else 
			{
				$backupFile = $backupFile;
			}
		}
		
		if(empty($tables))
		{
			$result = Yii::app()->db->createCommand("SHOW TABLES")->queryAll();
			foreach ($result as $k => $v)
			{
				$tables[] = $v['Tables_in_'.$this->getDbName()];
			}			
		}
		else 
		{
			foreach ($tables as $k=>$v)
			{
				$tables[$k] = Yii::app()->db->tablePrefix.$v;
			}
		}
		if($fp = fopen($backupFile, 'wb'))
		{
			if(Yii::app()->db->charset)
			{
				fwrite($fp, "SET NAMES ".Yii::app()->db->charset.";\n\n");
			}
			foreach ($tables as $table)
			{
//				echo $table;
				$this->dumpTable($table,$fp);
				fwrite($fp, "\n");
			}
			fclose($fp);
			return true;
		}
		else 
		{
			return false;
		}
	}
	
	//导出表格
	protected function dumpTable($table,$fp)
	{
		//备份表结构
		//fwrite($fp,"--\n--{{$table}}\n-- \n");
		$sql = "SHOW CREATE TABLE $table";
		$sql2 = "SELECT * FROM $table";
		$struct = Yii::app()->db->createCommand($sql)->queryRow();
		fwrite($fp, "DROP TABLE IF EXISTS `{$table}`;\n");
		fwrite($fp, str_replace("\n", "", $struct['Create Table']).";\n\n");
		$result = Yii::app()->db->createCommand($sql2)->queryAll();
		foreach ($result as $data)
		{
			foreach ($data as $k=>$v)
			{
				//把内容为''的字段变为"'NULL'"
				if($v == NULL)
				{
					$v = 'NULL';
				}
				$row[$k] = "'".mysql_escape_string($v)."'";
			}
			$sql = "INSERT INTO `$table` VALUES (".join(", ", $row).");\n";
			
			//替换空字段''为NULL
			$sql = str_replace("'NULL'",'NULL', $sql);
			fwrite($fp, $sql);
		}
		fwrite($fp, "\n");
//		var_dump($result);
	}
	
	//导出表格
	protected function dumpTableAsFile($table,$fp)
	{
		//备份表结构
		//fwrite($fp,"--\n--{{$table}}\n-- \n");
		$sql = "SHOW CREATE TABLE $table";
		$sql2 = "SELECT * FROM $table";
		$struct = Yii::app()->db->createCommand($sql)->queryRow();
		fwrite($fp, "DROP TABLE IF EXISTS `{$table}`;\n");
		fwrite($fp, str_replace("\n", "", $struct['Create Table']).";\n\n");
		$result = Yii::app()->db->createCommand($sql2)->queryAll();
		foreach ($result as $data)
		{
			foreach ($data as $k=>$v)
			{
				//把内容为''的字段变为"'NULL'"
				if($v == NULL)
				{
					$v = 'NULL';
				}
				$row[$k] = "'".mysql_escape_string($v)."'";
			}
			$sql = "INSERT INTO `$table` VALUES (".join(", ", $row).");\n";
			
			//替换空字段''为NULL
			$sql = str_replace("'NULL'",'NULL', $sql);
			fwrite($fp, $sql);
		}
		fwrite($fp, "\n");
//		var_dump($result);
	}
	
	//恢复数据库文件
	public function restore($backupFile)
	{
		if($fp = fopen($backupFile, 'r'))
		{
			$sql = '';
			while (!feof($fp)) 
			{
				$line = fgets($fp);
				if(strpos($line, '--')!==0)
				{
					$sql .= $line;
				}
				
				if(preg_match('/;\s*$/', $sql))
				{
//					echo $sql;
					Yii::app()->db->createCommand($sql)->query();
					$sql = '';
				}
			}
			fclose($fp);
			echo "OK";
			return true;
		}
		else 
		{
			return false;
		}
	}	
	
	public function showTables()
	{
		$tables = array();
		$result = Yii::app()->db->createCommand("SHOW TABLES")->queryAll();
		foreach ($result as $k => $v)
		{
			$tables[] = $v['Tables_in_'.$this->getDbName()];
		}
		return $tables;
	}
	
	function test(&$i)
	{
		return  $i++;
	}
	
	public function actionTest()
	{
		$i = 1;
		
		echo $this->test($i);
		echo $this->test($i);
		
// 		$article = "article";
// 		$sql = "SELECT * FROM  {{article}}";
		$sql = "SHOW CREATE TABLE {{{$article}}}";
// 		$result = Yii::app()->db->createCommand($sql)->queryAll();
		
// 		echo $result['Create Table'];
		
		echo $result->_getStatment;
// 		var_dump($result);
//		$fp = fopen('sql.sql', 'wb');
//		$this->dumpTable('article', $fp);
//		fclose($fp);
	}
	
	/***	复制数据库时所用
	 * DROP TABLE IF EXISTS `test`.`uu_article`;
CREATE TABLE `test`.`uu_article` ( `arc_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`arc_cid` varchar( 200 ) NOT NULL ,
`arc_title` varchar( 200 ) NOT NULL ,
`arc_iscomment` tinyint( 4 ) NOT NULL ,
`arc_author` int( 11 ) NOT NULL ,
`arc_hits` int( 11 ) NOT NULL ,
`arc_created` int( 11 ) NOT NULL ,
`arc_update` int( 11 ) NOT NULL ,
`arc_tags` varchar( 100 ) NOT NULL ,
`arc_state` int( 11 ) NOT NULL ,
`arc_content` text NOT NULL ,
PRIMARY KEY ( `arc_id` ) ) ENGINE = MyISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =35;

INSERT INTO `test`.`uu_article` SELECT * FROM `uurewrite`.`uu_article`;
	 */
	protected function blackLayout()
	{
		return $this->layout='application.modules.admin.views.layouts.blank';
	}

}